ajReadWorkbook function

Available since AlchemyJ v4.0

Description

The ajReadWorkbook function imports the content of an Excel file into a defined area in the Excel worksheet. It is mainly used for loading data from an Excel file for processing.

Syntax

ajReadWorkbook(source_workbook_path, data_range, [convert_to_text], [run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
source_workbook_path (required)	String	The path of the file to be read. Remark: The function only supports source workbook in xlsx, xlsm and xls format, it cannot be the same as current workbook.
data_range (required)	String	The range of data to read in the source workbook in [sheet]!range_address format. For example, "sheet1!A2:F10".
convert_to_text (optional)	Boolean	If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type of each cell. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Contents of specified data range in the selected file

2) Return Type: Multiple values (array formula)

Example

Here are some examples of using the ajReadWorkbook function.

Example 1 - Read all data from workbook sheet

Here is an excel file to be read by ajReadWorkbook function.

The file content and path as below,data from A1 to D6 is to be read to the AlchemyJ workbook.

The value of parameter data_range is the name of the sheet,which means reading the data of the entire sheet.

Of course, you can also specify the range of data to be read, and convert all data to text.

Example 2 - Dynamic the file path

For the workbook file path can be dynamic

Set the path parameter in %%AppConfig as below

Then use the file name to get the source workbook path as below

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid source workbook, it can be a xlsx, xlsm and xls file.
Invalid data range should be in [sheet name]![range address] format. For example, sheet1!B2:C3.
Data range does not exist.
Target workbook path is same as the path of the current workbook.